package br.senai.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import br.senai.entity.Aluno;
import br.senai.util.Conexao;

public class AlunoDAO {
	Connection connection;
	public AlunoDAO(){
		connection = Conexao.getConnection();

	}
	public boolean save(Aluno aluno) {
		PreparedStatement ps = null;
		try {
			ps = connection.prepareStatement("INSERT INTO aluno ( nome, cpf, telefone, email, endereco) VALUES ( ?, ?, ?, ?, ?);");
			ps.setString(1, aluno.getNome());
			ps.setString(2, aluno.getCpf());
			ps.setString(3, aluno.getTelefone());
			ps.setString(4, aluno.getEmail());
			ps.setString(5, aluno.getEndereco());

			ps.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (ps != null)
					ps.close();
				if (connection != null)
					connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return false;
	}

	public void edit(Aluno aluno) throws SQLException {
		PreparedStatement ps = null;
		try {
		    ps = connection.prepareStatement("UPDATE aluno SET nome =?, cpf =?, telefone =?, email =?, endereco =? WHERE matricula =?");

			 ps.setString(1, aluno.getNome());
			 ps.setString(2, aluno.getCpf());
			 ps.setString(3, aluno.getTelefone());
			 ps.setString(4, aluno.getEmail());
			 ps.setString(5, aluno.getEndereco());
			 ps.setLong(6, aluno.getMatricula());
			 ResultSet rs = ps.executeQuery();
			 rs.close();
			 ps.close();
			
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
	}
}
	public boolean delete(long matricula) { 
		PreparedStatement ps = null;
		try {
	        String sql = "DELETE FROM aluno WHERE matricula = " + matricula + ";";
			ps = connection.prepareStatement(sql);
			ps.executeUpdate();
			return true;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (ps != null)
					ps.close();
				if (connection != null)
					connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return false;
	}

	public List<Aluno> getAlunos() {
		List<Aluno> alunos = new ArrayList<Aluno>();
		PreparedStatement stmt = null;
		ResultSet rs = null;

		try {
			stmt = connection.prepareStatement(" SELECT * FROM aluno");
			rs = stmt.executeQuery();
			while (rs.next()) {
				Aluno aluno = new Aluno();
				aluno.setMatricula(rs.getLong("matricula"));
				aluno.setNome(rs.getString("nome"));
				aluno.setCpf(rs.getString("cpf"));
				aluno.setTelefone(rs.getString("telefone"));
				aluno.setEmail(rs.getString("email"));
				aluno.setEndereco(rs.getString("endereco"));
				alunos.add(aluno);
			}
		} catch (SQLException e) {
			e.printStackTrace();	
		}
		return alunos;
	}
	public Aluno buscarAlunoCpf(String cpf) throws SQLException{
		Connection connection = Conexao.getConnection();
		try {
			PreparedStatement stmt = connection.prepareStatement("select * from  aluno where cpf = ?");
			stmt.setString(1, cpf);
			ResultSet rs = stmt.executeQuery();
				while(rs.next()){
					Aluno aluno = new Aluno();
					aluno.setMatricula(rs.getLong("matricula"));
					aluno.setNome("nome");
					aluno.setEmail("email");
					aluno.setCpf("cpf");
					aluno.setEndereco("endereco");
					aluno.setTelefone("telefone");
					return aluno;
			}
				rs.close();
				stmt.close();
		} catch (Exception e) {
			e.getMessage();
		}
			return null;
	}
	public Aluno buscarAlunoMatricula(Long matricula) throws SQLException{
		Connection connection = Conexao.getConnection();
		try {
			PreparedStatement stmt = connection.prepareStatement("select * from  aluno where matricula = ?");
			stmt.setLong(1, matricula);
			ResultSet rs = stmt.executeQuery();
				while(rs.next()){
					Aluno aluno = new Aluno();
					aluno.setMatricula(rs.getLong("matricula"));
					aluno.setNome("nome");
					aluno.setEmail("email");
					aluno.setCpf("cpf");
					aluno.setEndereco("endereco");
					aluno.setTelefone("telefone");
					return aluno;
			}
				rs.close();
				stmt.close();
		} catch (Exception e) {
			e.getMessage();
		}
		return null;
	}
}
